"""
数据库的连接与关闭

对数据库写操作会默认开启事务，
如果数据表支持事务需要commit提交才能生效，
如果数据表不支持事务则execute执行语句后立即生效
"""
import pymysql

# 参数字典
kwargs = {
    "host":"localhost",
    "port": 3306,
    "user": "root",
    "password": "root",
    "database": "stu",
    "charset": "utf8"
}

# 连接数据库
db = pymysql.connect(**kwargs)

# 生成游标：执行sql操作数据，得到操作结果的对象
cur = db.cursor()
# score = 99
#
# id = 1
#
# try:
#     # 数据操作
#     # sql = "update class set score=%s where id=%s;"
#     sql = "update class set score=%s where name=%s"
#     cur.execute(sql,[99,"Lily"])
#     sql1 = "delete from class where score<%s;"
#     cur.execute(sql1,[60])
# except Exception as e:
#     print(e)
#     db.rollback()
# else:
#     db.commit() # 提交事务
#
# # 关闭
# cur.close()
# db.close()




try:
    # 数据操作
    # sql = "update class set score=%s where id=%s;"
    # sql = "update class set score=%s where name=%s"
    # cur.execute(sql,[99,"Lily"])
    # sql1 = "delete from class where score<%s;"
    # cur.execute(sql1,[60])
    data = [("zhang",16,'m',70),("wang",17,'m',69),("lisi",18,'w',71)]
    sql = "insert into class (name,age,sex,score) values(%s,%s,%s,%s)"
    cur.executemany(sql,data)
except Exception as e:
    print(e)
    db.rollback()
else:
    db.commit() # 提交事务

# 关闭
cur.close()
db.close()